package com.xesj.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;


/** 
* @ClassName: ExcellUtil 
* @Description: 定义Excell工具类
* @author Du Wince
* @date 2016年4月25日 下午2:15:18 
*  
*/
public class ExcellUtil 
{

	/** 
	 * @Title: exportExcell 
	 * @Description: 按指定信息导出Excell
	 * @param headers 表头信息
	 * @param attrs 每行对应表头的属性信息,也是pds的key值
	 * @param pds 要导出的数据
	 * @param exportPath 要导出的路径(不能以'\\'或者'/'结尾)
	 * @param fileName 文件名称
	 * @return 
	 * @author Du Wince 
	 * @throws IOException 
	 * @throws FileNotFoundException 
	 * @date 2016年4月25日 下午2:21:42 
	 */
	public static String exportExcell(String [] headers, String [] attrs, List<PageData> pdsList, String exportPath, String fileName) throws FileNotFoundException, IOException
	{
		if ( headers == null || headers.length != attrs.length )
		{
			return "";
		}
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet hssfSheet = workbook.createSheet();
		HSSFCellStyle style = workbook.createCellStyle();
		style.setFillBackgroundColor(CellStyle.LEAST_DOTS);
		style.setFillPattern(CellStyle.LEAST_DOTS);

		// 设置Excel中的边框(表头的边框)
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setBorderBottom(CellStyle.BORDER_MEDIUM);
		style.setBottomBorderColor(HSSFColor.BLACK.index);
		style.setBorderLeft(CellStyle.BORDER_MEDIUM);
		style.setLeftBorderColor(HSSFColor.BLACK.index);
		style.setBorderRight(CellStyle.BORDER_MEDIUM);
		style.setRightBorderColor(HSSFColor.BLACK.index);
		style.setBorderTop(CellStyle.BORDER_MEDIUM);
		style.setTopBorderColor(HSSFColor.BLACK.index);

		// 设置字体
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 14); // 字体高度
		font.setFontName("黑体 "); // 字体

		style.setFont(font);
		HSSFRow firstRow = hssfSheet.createRow((short) 0);
		
		// 获取行单元格个数
		int headersLength = headers.length;
		// 循环单元格，设置表字段
		for ( int i = 0; i < headersLength; i ++ )
		{
			HSSFCell firstCell = firstRow.createCell( i );
			firstRow.setHeight((short) 400);
			// 设置Excel中的背景
			style.setFillForegroundColor( HSSFColor.GREEN.index );
			style.setFillBackgroundColor( HSSFColor.GREEN.index );
			firstCell.setCellValue( new HSSFRichTextString( headers[i] ) );
			firstCell.setCellStyle( style );
		}

		if ( pdsList != null && pdsList.size() > 0 )
		{
			// 设置Excel中的边框
			HSSFCellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
			cellStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
			cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
			cellStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
			cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
			cellStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
			cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
			cellStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
			cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
			
			// 获取行数
			int columnContentLength = pdsList.size();
			// 循环表行数
			for ( int rowCount = 1; rowCount <= columnContentLength; rowCount ++ ) 
			{
				
				HSSFRow hssfRow = hssfSheet.createRow((short) rowCount);
				for ( int cellCount = 0; cellCount < headersLength; cellCount ++ )
				{
					HSSFCell firstHssfCell = hssfRow.createCell( cellCount );// 商品编号
					firstHssfCell.setCellType( Cell.CELL_TYPE_NUMERIC );
					String attribute = attrs[ cellCount ];
					String value =  pdsList.get( rowCount - 1 ).get( attribute ) + "";
					if(StrValidate.isNull(value)||"null".equals(value)){
						value="";
					}
					firstHssfCell.setCellValue( new HSSFRichTextString( value ) );
					firstHssfCell.setCellStyle( cellStyle );// 设置单元格的样式
				}
			}
			
			File file=new File( exportPath );  
			if( !file.exists() )  
			{  
			    try 
			    {  
			        file.mkdirs(); 
			    } 
			    catch (Exception e) 
			    {  
			        e.printStackTrace();  
			    }  
			}  
			file = new File( exportPath + "\\" + fileName );
			FileOutputStream fOut = new FileOutputStream(file);
			workbook.write(fOut);
			fOut.flush();
			fOut.close();
			workbook.close();
			return "true";
		}
		else 
		{
			workbook.close();
			return "查无数据";
		}
	}
	
	/** 
	* @Title: shipperImportExcellFile 
	* @Description: 配送商通过excell文件导入资料（所有文件表头分为2行，第一行是文件介绍；第二行表头信息）。<br>
	* 导入的文件，统一存入路径：root[root视具体情况而定]/shipperImpFile/fileName.?
	* @author Du Wince
	* @param req
	* @return
	* @throws Exception
	* @创建时间 2018年3月8日下午5:32:13
	*/
	public static PageData shipperImportExcellFile(HttpServletRequest req) throws Exception
	{
		PageData rePd = new PageData();
		
		//创建一个通用的多部分解析器
		CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(req.getSession().getServletContext());
		//判断 request 是否有文件上传,即多部分请求  
		if(multipartResolver.isMultipart(req))
		{
			//转换成多部分request    
			MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest)req;  
			//取得request中的所有文件名  
			Iterator<String> iter = multiRequest.getFileNames();
			int i = 0;
			while(iter.hasNext())
			{
				//取得上传文件 
				MultipartFile file = multiRequest.getFile(iter.next()); 
				
				if(file != null)
				{
					//上传表单文件控件名称
					String name = file.getName();
					//上传文件原名称
					String orgName = file.getOriginalFilename();
					int lastIndex = orgName.lastIndexOf(".");
					//后缀名
					String ext = orgName.substring(lastIndex, orgName.length());
					long times = System.currentTimeMillis() + i;
			
					String fileName =   times+"_" + ext;
					
					//file path
					String classPath = ExcellUtil.class.getResource( "/" ).getPath();
					String webRootPath = classPath.substring( 0, classPath.lastIndexOf( "WEB-INF" ) );
					String filePath = webRootPath + "shipperImpFile/" + fileName;
					rePd.put("filePath", filePath);
					
					File transFile = new File(filePath);
					if (!transFile.exists())
					{
						transFile.mkdirs();
					}
					file.transferTo(transFile);
				}
			}
		}
		
		
		return rePd;
	}
	
	/** 
	* @Title: readExcellData 
	* @Description: 读取已导入的excell文件数据（正式数据从第三行开始读取）
	* @author Du Wince
	* @param filePath 文件绝对路径
	* @return 将读取的文件放入list中，返回.list中存放的PageData对象。key：为单元格下标，value：单元格内容
	* @创建时间 2018年3月8日下午6:10:27
	*/
	public static List<PageData> readExcellData(String filePath)
	{
		//文件流对象
		POIFSFileSystem pfs = null;
		//工作簿对象
	    Workbook wb = null;
		
		List<PageData> dataList = new ArrayList<PageData>();
		File file = new File(filePath);
		
		if (!file.exists())
		{
			return dataList;
		}
		try 
		{			
			//装载excell文件到流中
			FileInputStream fis=new FileInputStream(file);
			//pfs = new POIFSFileSystem(file);
			//通过流创建工作簿
			try {
				wb = new HSSFWorkbook(fis);
			} catch (Exception e) {
				// TODO: handle exception
				wb = new XSSFWorkbook(fis); 
			}
			
			//获取工作簿的第一个表格
			HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);
			//总行数
			int sheetRows = sheet.getLastRowNum();
			HSSFRow row = sheet.getRow(2);
			if (row == null)
			{
				return dataList;
			}
			//总列数
			int sheetCols = row.getLastCellNum();
			
			int i = 2;
			//从第二行开始读取excell文件信息
			for (; i < (sheetRows + 1); i ++)
			{
				//获取行数据
				row = sheet.getRow(i);
				PageData data = new PageData();
				for (int y = 0; y < sheetCols; y ++)
				{
					int key = y;
					HSSFCell cell = row.getCell(y);
					String value = null;
					if (cell != null)
					{
						
					    cell.setCellType(Cell.CELL_TYPE_STRING);
						//获取单元格数据
						value = cell.getStringCellValue();
					}
					data.put(key, value);
				}
				dataList.add(data);
			}
			
			return dataList;
		} 
		catch (IOException e) 
		{
			e.printStackTrace();
			return dataList;
		}
		finally
		{
			if (wb != null)
			{
				try 
				{
					wb.close();
				} 
				catch (IOException e) 
				{
					e.printStackTrace();
				}
			}
			
			if (pfs != null)
			{
				try 
				{
					pfs.close();
				} 
				catch (IOException e) 
				{
					e.printStackTrace();
				}
			}
		}
	}
} 